﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CMISLibrary.Entities;
using CMISLibrary;
using System.Globalization;
using BCNPCObject.DB;


namespace BCNPCObject
{
    public class NPC_CHAMNO_OFFLINE_Control
    {
        private NPC_CHAMNO_OFFLINE_Info info;
        private DataSet data;
        public DateTime date = System.DateTime.Now;
        public NPC_CHAMNO_OFFLINE_Control()
        {
            info = new NPC_CHAMNO_OFFLINE_Info();
        }

        public NPC_CHAMNO_OFFLINE_Control(NPC_CHAMNO_OFFLINE_Info INFO)
        {
            info = INFO;
        }

        public DataSet DS_TNGAN_DACHAM(string ma_dvql)
        {
            data = new DataSet();
            Int64[] id_chamno_off;
            string[] tngan;

            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dvql
                             select a.ID_HDON).ToArray();

            var chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                              where a.MA_DVIQLY == ma_dvql
                              select a).ToList();

            if (id_chamno_off.Count() == 0) return null;

            var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                             where a.MA_DVIQLY == ma_dvql && id_chamno_off.Contains(a.ID_HDON)
                             select a).ToList();

            var t_ngan = (from a in CMIS2.DB.CN_BB_GTHU
                          join b in CMIS2.DB.D_TNGAN_VIEN on a.MA_TNGAN equals b.MA_TNGAN
                          where a.MA_DVIQLY == ma_dvql
                          select new { a.SO_BBGIAO, b.TEN_TNGAN, b.MA_TNGAN }).ToList();
            var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                where a.MA_DVIQLY == ma_dvql
                                select a).ToList();

            var hdon_tngan = (from a in cn_hdon_gthu
                              join b in t_ngan on a.SO_BBGIAO equals b.SO_BBGIAO
                              select new { a.ID_HDON, b.TEN_TNGAN, b.MA_TNGAN }).ToList();

            var ketqua = (from a in hdon_tngan
                          join b in theodoino on a.ID_HDON equals b.ID_HDON
                          select new { a.MA_TNGAN }).ToList();

            tngan = ketqua.Select(c => c.MA_TNGAN).Distinct().ToArray();

            var thu_ngan = (from a in CMIS2.DB.D_TNGAN_VIEN
                            where tngan.Contains(a.MA_TNGAN)
                            select new 
                            {
                                a.MA_TNGAN,
                                a.TEN_TNGAN
                            }).ToList();

            DataTable dt = Utility.LINQToDataTable(thu_ngan);
            if (dt == null) return null;
            data.Tables.Add(dt);
            return data;
        }

        public DataSet LOAD_DACHAM_All(string ma_tngan, string ma_dvql)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_chamno_off;

            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dvql && a.MA_TNGAN == ma_tngan
                             select a.ID_HDON).ToArray();

            var chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                              where a.MA_DVIQLY == ma_dvql
                              select a).ToList();

            if (id_chamno_off.Count() == 0) return null;

            var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                             where id_chamno_off.Contains(a.ID_HDON)
                             select a).ToList();
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dvql
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao.Count() > 0 && so_bbgiao != null)
            {
                var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                              where a.MA_DVIQLY == ma_dvql
                              select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
                var ketqua = (from a in bbgiao
                              join b in theodoino on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  b.ID_HDON,
                                  b.MA_KHANG,
                                  b.TEN_KHANG,
                                  b.DCHI_KHANG,
                                  TIEN_NOP = b.TIEN_NO,
                                  THUE_NOP = b.THUE_NO,
                                  TTIEN = b.TIEN_NO + b.THUE_NO,
                                  a.SO_BBGIAO,
                                  a.MA_SOGCS,
                                  b.KY,
                                  b.THANG,
                                  b.NAM
                              }).ToList();
                var kq = (from a in ketqua
                          join b in chamno_off on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              a.ID_HDON,
                              a.MA_KHANG,
                              a.TEN_KHANG,
                              a.DCHI_KHANG,
                              a.TIEN_NOP,
                              a.THUE_NOP,
                              a.TTIEN,
                              a.SO_BBGIAO,
                              a.MA_SOGCS,
                              a.KY,
                              a.THANG,
                              a.NAM,
                              b.NGAY_CHAMNO
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(ketqua);
                if (dt == null) return null;
                data.Tables.Add(dt);
                return data;
            }
            else return null;

        }

        public DataSet DS_DACHAM_All(string ma_dvql)
        {
            data = new DataSet();
            Int64[] id_chamno_off;

            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dvql
                             select a.ID_HDON).ToArray();

            var chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                              where a.MA_DVIQLY == ma_dvql
                              select a).ToList();

            if (id_chamno_off.Count() == 0) return null;

            var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                             where a.MA_DVIQLY == ma_dvql && id_chamno_off.Contains(a.ID_HDON)
                             select a).ToList();
            var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                          where a.MA_DVIQLY == ma_dvql
                          select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
            var ketqua = (from a in bbgiao
                          join b in theodoino on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              b.ID_HDON,
                              b.MA_KHANG,
                              b.TEN_KHANG,
                              b.DCHI_KHANG,
                              TIEN_NOP = b.TIEN_NO,
                              THUE_NOP = b.THUE_NO,
                              TTIEN = b.TIEN_NO + b.THUE_NO,
                              a.SO_BBGIAO,
                              a.MA_SOGCS,
                              b.KY,
                              b.THANG,
                              b.NAM
                          }).ToList();
            var kq = (from a in ketqua
                      join b in chamno_off on a.ID_HDON equals b.ID_HDON
                      select new
                      {
                          a.ID_HDON,
                          a.MA_KHANG,
                          a.TEN_KHANG,
                          a.DCHI_KHANG,
                          a.TIEN_NOP,
                          a.THUE_NOP,
                          a.TTIEN,
                          a.SO_BBGIAO,
                          a.MA_SOGCS,
                          a.KY,
                          a.THANG,
                          a.NAM,
                          NGAY_CHAMNO = b.NGAY_CHAMNO,
                          b.THANG_HT
                      }).ToList();
            DataTable dt = Utility.LINQToDataTable(kq);
            if (dt == null) return null;
            data.Tables.Add(dt);
            return data;
        }

        public DataSet DS_DACHAM_NUTCHA(string ma_tngan, string ma_dvql)
        {
            data = new DataSet();
            Int64[] id_chamno_off;

            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dvql && a.MA_TNGAN == ma_tngan
                             select a.ID_HDON).ToArray();

            var chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                              where a.MA_DVIQLY == ma_dvql
                              select a).ToList();

            if (id_chamno_off.Count() == 0) return null;

            var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                             where a.MA_DVIQLY == ma_dvql && id_chamno_off.Contains(a.ID_HDON)
                             select a).ToList();
            var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                          where a.MA_DVIQLY == ma_dvql
                          select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
            var ketqua = (from a in bbgiao
                          join b in theodoino on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              b.ID_HDON,
                              b.MA_KHANG,
                              b.TEN_KHANG,
                              b.DCHI_KHANG,
                              TIEN_NOP = b.TIEN_NO,
                              THUE_NOP = b.THUE_NO,
                              TTIEN = b.TIEN_NO + b.THUE_NO,
                              a.SO_BBGIAO,
                              a.MA_SOGCS,
                              b.KY,
                              b.THANG,
                              b.NAM
                          }).ToList();
            var kq = (from a in ketqua
                      join b in chamno_off on a.ID_HDON equals b.ID_HDON
                      select new
                      {
                          a.ID_HDON,
                          a.MA_KHANG,
                          a.TEN_KHANG,
                          a.DCHI_KHANG,
                          a.TIEN_NOP,
                          a.THUE_NOP,
                          a.TTIEN,
                          a.SO_BBGIAO,
                          a.MA_SOGCS,
                          a.KY,
                          a.THANG,
                          a.NAM,
                          b.NGAY_CHAMNO,
                          b.THANG_HT
                      }).ToList();
            DataTable dt = Utility.LINQToDataTable(kq);
            if (dt == null) return null;
            data.Tables.Add(dt);
            return data;
        }

        public DataSet DS_DACHAM_NUTCHA2(int bb_giao, string ma_tngan, string ma_dvql)
        {
            data = new DataSet();
            Int64[] id_chamno_off;

            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dvql && a.MA_TNGAN == ma_tngan
                             select a.ID_HDON).ToArray();

            var chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                              where a.MA_DVIQLY == ma_dvql
                              select a).ToList();

            if (id_chamno_off.Count() == 0) return null;

            var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                             where a.MA_DVIQLY == ma_dvql && id_chamno_off.Contains(a.ID_HDON)
                             select a).ToList();
            var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                          where a.MA_DVIQLY == ma_dvql && a.SO_BBGIAO == bb_giao
                          select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
            var ketqua = (from a in bbgiao
                          join b in theodoino on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              b.ID_HDON,
                              b.MA_KHANG,
                              b.TEN_KHANG,
                              b.DCHI_KHANG,
                              TIEN_NOP = b.TIEN_NO,
                              THUE_NOP = b.THUE_NO,
                              TTIEN = b.TIEN_NO + b.THUE_NO,
                              a.SO_BBGIAO,
                              a.MA_SOGCS,
                              b.KY,
                              b.THANG,
                              b.NAM
                          }).ToList();
            var kq = (from a in ketqua
                      join b in chamno_off on a.ID_HDON equals b.ID_HDON
                      select new
                      {
                          a.ID_HDON,
                          a.MA_KHANG,
                          a.TEN_KHANG,
                          a.DCHI_KHANG,
                          a.TIEN_NOP,
                          a.THUE_NOP,
                          a.TTIEN,
                          a.SO_BBGIAO,
                          a.MA_SOGCS,
                          a.KY,
                          a.THANG,
                          a.NAM,
                          NGAY_CHAMNO = b.NGAY_CHAMNO,
                          b.THANG_HT
                      }).ToList();
            DataTable dt = Utility.LINQToDataTable(kq);
            if (dt == null) return null;
            data.Tables.Add(dt);
            return data;
        }

        public DataSet DS_DACHAM_NUTCON(string so_gcs, int bb_giao, string ma_tngan, string ma_dvql)
        {
            data = new DataSet();
            Int64[] id_chamno_off;

            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dvql && a.MA_TNGAN == ma_tngan
                             select a.ID_HDON).ToArray();

            var chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                              where a.MA_DVIQLY == ma_dvql
                              select a).ToList();

            if (id_chamno_off.Count() == 0) return null;

            var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                             where a.MA_DVIQLY == ma_dvql && id_chamno_off.Contains(a.ID_HDON)
                             select a).ToList();
            var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                          where a.MA_DVIQLY == ma_dvql && a.SO_BBGIAO == bb_giao && a.MA_SOGCS == so_gcs
                          select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
            var ketqua = (from a in bbgiao
                          join b in theodoino on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              b.ID_HDON,
                              b.MA_KHANG,
                              b.TEN_KHANG,
                              b.DCHI_KHANG,
                              TIEN_NOP = b.TIEN_NO,
                              THUE_NOP = b.THUE_NO,
                              TTIEN = b.TIEN_NO + b.THUE_NO,
                              a.SO_BBGIAO,
                              a.MA_SOGCS,
                              b.KY,
                              b.THANG,
                              b.NAM
                          }).ToList();
            var kq = (from a in ketqua
                      join b in chamno_off on a.ID_HDON equals b.ID_HDON
                      select new
                      {
                          a.ID_HDON,
                          a.MA_KHANG,
                          a.TEN_KHANG,
                          a.DCHI_KHANG,
                          a.TIEN_NOP,
                          a.THUE_NOP,
                          a.TTIEN,
                          a.SO_BBGIAO,
                          a.MA_SOGCS,
                          a.KY,
                          a.THANG,
                          a.NAM,
                          NGAY_CHAMNO = b.NGAY_CHAMNO,
                          b.THANG_HT
                      }).ToList();
            DataTable dt = Utility.LINQToDataTable(kq);
            if (dt == null) return null;
            data.Tables.Add(dt);
            return data;
        }

        public DataSet ChamnoOff_All(string ma_dvql)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            var D_TNGAN_VIEN = (from a in CMIS2.DB.D_TNGAN_VIEN
                                where a.MA_DVIQLY == ma_dvql
                                select new { a.MA_TNGAN, a.TEN_TNGAN }).ToList();
            var CN_THEODOINO = (from a in CMIS2.DB.CN_THEODOINO
                                where a.MA_DVIQLY == ma_dvql
                                select a).ToList();

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dvql
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao.Count() > 0 && so_bbgiao != null)
            {
                var CN_BB_GTHU = (from a in CMIS2.DB.CN_BB_GTHU
                                  where a.MA_DVIQLY == ma_dvql
                                  select new { a.SO_BBGIAO, a.NGAY_GIAO, a.MA_TNGAN }).ToList();
                var BB_TNV = (from a in D_TNGAN_VIEN
                              join b in CN_BB_GTHU on a.MA_TNGAN equals b.MA_TNGAN
                              select new { a.MA_TNGAN, a.TEN_TNGAN, b.SO_BBGIAO, b.NGAY_GIAO }).ToList();
                var CN_HDON_GTHU = (from a in CMIS2.DB.CN_HDON_GTHU
                                    where so_bbgiao.Contains(a.SO_BBGIAO)
                                    select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
                var BB_HDON_GTHU = (from a in BB_TNV
                                    join b in CN_HDON_GTHU on a.SO_BBGIAO equals b.SO_BBGIAO
                                    select new { a.NGAY_GIAO, b.MA_SOGCS, b.ID_HDON, b.SO_BBGIAO, a.MA_TNGAN, a.TEN_TNGAN }).ToList();
                var ketqua = (from a in BB_HDON_GTHU
                              join b in CN_THEODOINO on a.ID_HDON equals b.ID_HDON
                              orderby b.STT
                              select new
                              {
                                  b.ID_HDON,
                                  b.MA_KHANG,
                                  b.TEN_KHANG,
                                  b.DCHI_KHANG,
                                  TIEN_NOP = b.TIEN_NO,
                                  THUE_NOP = b.THUE_NO,
                                  TTIEN = b.TIEN_NO + b.THUE_NO,
                                  a.SO_BBGIAO,
                                  a.MA_SOGCS,
                                  a.NGAY_GIAO,
                                  a.TEN_TNGAN,
                                  a.MA_TNGAN,
                                  b.KY,
                                  b.THANG,
                                  b.NAM,
                                  b.SO_SERY,
                                  MA_KVUC = b.STT,
                                  b.HTHUC_TTOAN,
                                  TEN_DCHI = b.TEN_KHANG + " - " + b.DCHI_KHANG,
                                  Tien = b.TIEN_PSINH + b.THUE_PSINH
                              }).ToList();
                DataTable dt = Utility.LINQToDataTable(ketqua);
                if (dt == null) return null;
                data.Tables.Add(dt);
                return data;
            }
            else return null;
        }

        public DataSet ChamnoOff_Nutcha(string ma_tngan, string ma_dvql)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            var D_TNGAN_VIEN = (from a in CMIS2.DB.D_TNGAN_VIEN
                                where a.MA_DVIQLY == ma_dvql
                                select new { a.MA_TNGAN, a.TEN_TNGAN }).ToList();
            var CN_THEODOINO = (from a in CMIS2.DB.CN_THEODOINO
                                where a.MA_DVIQLY == ma_dvql
                                select a).ToList();

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dvql && a.MA_TNGAN == ma_tngan
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao.Count() > 0)
            {
                var CN_BB_GTHU = (from a in CMIS2.DB.CN_BB_GTHU
                                  where a.MA_DVIQLY == ma_dvql
                                  select new { a.SO_BBGIAO, a.NGAY_GIAO, a.MA_TNGAN }).ToList();
                var BB_TNV = (from a in D_TNGAN_VIEN
                              join b in CN_BB_GTHU on a.MA_TNGAN equals b.MA_TNGAN
                              select new { a.MA_TNGAN, a.TEN_TNGAN, b.SO_BBGIAO, b.NGAY_GIAO }).ToList();
                var CN_HDON_GTHU = (from a in CMIS2.DB.CN_HDON_GTHU
                                    where so_bbgiao.Contains(a.SO_BBGIAO) && a.MA_DVIQLY == ma_dvql
                                    select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
                var BB_HDON_GTHU = (from a in BB_TNV
                                    join b in CN_HDON_GTHU on a.SO_BBGIAO equals b.SO_BBGIAO
                                    select new { a.NGAY_GIAO, b.MA_SOGCS, b.ID_HDON, b.SO_BBGIAO, a.MA_TNGAN, a.TEN_TNGAN }).ToList();
                var ketqua = from a in BB_HDON_GTHU
                             join b in CN_THEODOINO on a.ID_HDON equals b.ID_HDON
                             orderby b.STT
                             select new
                             {
                                 b.ID_HDON,
                                 b.MA_KHANG,
                                 b.TEN_KHANG,
                                 b.DCHI_KHANG,
                                 TIEN_NOP = b.TIEN_NO,
                                 THUE_NOP = b.THUE_NO,
                                 TTIEN = b.TIEN_NO + b.THUE_NO,
                                 a.SO_BBGIAO,
                                 a.MA_SOGCS,
                                 a.MA_TNGAN,
                                 a.TEN_TNGAN,
                                 b.KY,
                                 b.THANG,
                                 b.NAM,
                                 b.SO_SERY,
                                 MA_KVUC = b.MA_KVUC + " " + b.STT,
                                 b.HTHUC_TTOAN,
                                 a.NGAY_GIAO,
                                 TEN_DCHI = b.TEN_KHANG + " - " + b.DCHI_KHANG,
                                 Tien = b.TIEN_PSINH + b.THUE_PSINH
                             };
                DataTable dt = Utility.LINQToDataTable(ketqua);
                if (dt == null) return null;
                data.Tables.Add(dt);
                return data;
            }
            return null;

        }

        public DataSet ChamnoOff_Nutcha2(int bb_giao, string ma_tngan, string ma_dvql)
        {
            DataSet ds = new DataSet();
            var D_TNGAN_VIEN = (from a in CMIS2.DB.D_TNGAN_VIEN
                                where a.MA_DVIQLY == ma_dvql
                                select new { a.MA_TNGAN, a.TEN_TNGAN }).ToList();
            var CN_THEODOINO = (from a in CMIS2.DB.CN_THEODOINO
                                where a.MA_DVIQLY == ma_dvql
                                select a).ToList();
            var CN_BB_GTHU = (from a in CMIS2.DB.CN_BB_GTHU
                              where a.MA_DVIQLY == ma_dvql
                              select new { a.SO_BBGIAO, a.NGAY_GIAO, a.MA_TNGAN }).ToList();
            var BB_TNV = (from a in D_TNGAN_VIEN
                          join b in CN_BB_GTHU on a.MA_TNGAN equals b.MA_TNGAN
                          select new { a.MA_TNGAN, a.TEN_TNGAN, b.SO_BBGIAO, b.NGAY_GIAO }).ToList();
            var CN_HDON_GTHU = (from a in CMIS2.DB.CN_HDON_GTHU
                                where a.SO_BBGIAO == bb_giao && a.MA_DVIQLY == ma_dvql
                                select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
            var BB_HDON_GTHU = (from a in BB_TNV
                                join b in CN_HDON_GTHU on a.SO_BBGIAO equals b.SO_BBGIAO
                                select new { a.NGAY_GIAO, b.MA_SOGCS, b.ID_HDON, b.SO_BBGIAO, a.TEN_TNGAN, a.MA_TNGAN }).ToList();
            var ketqua = from a in BB_HDON_GTHU
                         join b in CN_THEODOINO on a.ID_HDON equals b.ID_HDON
                         orderby b.STT
                         select new
                         {
                             b.ID_HDON,
                             b.MA_KHANG,
                             b.TEN_KHANG,
                             b.DCHI_KHANG,
                             TIEN_NOP = b.TIEN_NO,
                             THUE_NOP = b.THUE_NO,
                             TTIEN = b.TIEN_NO + b.THUE_NO,
                             a.SO_BBGIAO,
                             a.MA_SOGCS,
                             a.TEN_TNGAN,
                             a.MA_TNGAN,
                             b.KY,
                             b.THANG,
                             b.NAM,
                             b.SO_SERY,
                             MA_KVUC = b.MA_KVUC + " " + b.STT,
                             b.HTHUC_TTOAN,
                             a.NGAY_GIAO,
                             TEN_DCHI = b.TEN_KHANG + " - " + b.DCHI_KHANG,
                             Tien = b.TIEN_PSINH + b.THUE_PSINH
                         };
            DataTable dt = Utility.LINQToDataTable(ketqua);
            if (dt == null) return null;
            ds.Tables.Add(dt);
            return ds;

        }

        public DataSet ChamnoOff_Nutcon(string so_gcs, int bb_giao, string ma_tngan, string ma_dvql)
        {
            DataSet ds = new DataSet();
            var D_TNGAN_VIEN = (from a in CMIS2.DB.D_TNGAN_VIEN
                                where a.MA_DVIQLY == ma_dvql
                                select new { a.MA_TNGAN, a.TEN_TNGAN }).ToList();
            var CN_BB_GTHU = (from a in CMIS2.DB.CN_BB_GTHU
                              where a.MA_DVIQLY == ma_dvql
                              select new { a.SO_BBGIAO, a.NGAY_GIAO, a.MA_TNGAN }).ToList();
            var BB_TNV = (from a in D_TNGAN_VIEN
                          join b in CN_BB_GTHU on a.MA_TNGAN equals b.MA_TNGAN
                          select new { a.MA_TNGAN, a.TEN_TNGAN, b.SO_BBGIAO, b.NGAY_GIAO }).ToList();
            var CN_THEODOINO = (from a in CMIS2.DB.CN_THEODOINO
                                where a.MA_DVIQLY == ma_dvql
                                select a).ToList();

            var CN_HDON_GTHU = (from a in CMIS2.DB.CN_HDON_GTHU
                                where a.MA_SOGCS == so_gcs && a.MA_DVIQLY == ma_dvql && a.SO_BBGIAO == bb_giao
                                select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();
            var BB_HDON_GTHU = (from a in BB_TNV
                                join b in CN_HDON_GTHU on a.SO_BBGIAO equals b.SO_BBGIAO
                                select new { a.NGAY_GIAO, b.MA_SOGCS, b.ID_HDON, b.SO_BBGIAO, a.TEN_TNGAN, a.MA_TNGAN }).ToList();
            var ketqua = from a in BB_HDON_GTHU
                         join b in CN_THEODOINO on a.ID_HDON equals b.ID_HDON
                         orderby b.STT
                         select new
                         {
                             b.ID_HDON,
                             b.MA_KHANG,
                             b.TEN_KHANG,
                             b.DCHI_KHANG,
                             TIEN_NOP = b.TIEN_NO,
                             THUE_NOP = b.THUE_NO,
                             TTIEN = b.TIEN_NO + b.THUE_NO,
                             a.SO_BBGIAO,
                             a.MA_SOGCS,
                             a.MA_TNGAN,
                             a.TEN_TNGAN,
                             b.KY,
                             b.THANG,
                             b.NAM,
                             b.SO_SERY,
                             MA_KVUC = b.MA_KVUC + " " + b.STT,
                             b.HTHUC_TTOAN,
                             a.NGAY_GIAO,
                             TEN_DCHI = b.TEN_KHANG + " - " + b.DCHI_KHANG,
                             Tien = b.TIEN_PSINH + b.THUE_PSINH
                         };
            DataTable dt = Utility.LINQToDataTable(ketqua);
            if (dt == null) return null;
            ds.Tables.Add(dt);
            return ds;

        }

        public DataSet DS_BBG(string ma_tngan, string ma_dvqly)
        {
            Int64[] id_hdon;
            Int64[] so_bbgiao;
            Int64[] id_hdon_CNOff;

            id_hdon_CNOff = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                             select a.ID_HDON).ToArray();

            if (id_hdon_CNOff.Count() == 0) return null;
            var cn_bb_gthu = (from a in CMIS2.DB.CN_BB_GTHU
                              where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                              select new { a.SO_BBGIAO }).ToList();
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao.Count() > 0)
            {
                var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                    where a.MA_DVIQLY == ma_dvqly && so_bbgiao.Contains(a.SO_BBGIAO)
                                    select new { a.ID_HDON, a.SO_BBGIAO }).ToList();

                id_hdon = (from a in CMIS2.DB.CN_HDON_GTHU
                           where a.MA_DVIQLY == ma_dvqly && so_bbgiao.Contains(a.SO_BBGIAO)
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.TIEN_NO > 0 && a.TTRANG_SSAI != 2 && a.MA_DVIQLY == ma_dvqly //&& id_hdon_CNOff.Contains(a.ID_HDON)
                                        select new
                                        {
                                            a.KY,
                                            a.THANG,
                                            a.NAM,
                                            a.MA_SOGCS,
                                            a.TIEN_NO,
                                            a.THUE_NO,
                                            a.ID_HDON,
                                            a.MA_KHANG,
                                            a.TEN_KHANG,
                                            a.LOAI_HDON,
                                            a.DCHI_KHANG
                                        }).ToList();
                    var kq = (from a in cn_theodoino
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  a.MA_SOGCS,
                                  TIEN_NOP = a.TIEN_NO,
                                  THUE_NOP = a.THUE_NO,
                                  TTIEN_NOP = a.TIEN_NO + a.THUE_NO,
                                  a.ID_HDON,
                                  b.SO_BBGIAO,
                                  MA_KHANG = a.MA_KHANG,
                                  LOAI_HDON = a.LOAI_HDON,
                                  TEN_KHANG = a.TEN_KHANG,
                                  DCHI_KHANG = a.DCHI_KHANG
                              }).ToList();
                    DataSet ds = new DataSet();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    if (dt == null) return null;
                    ds.Tables.Add(dt);
                    return ds;
                }
                return null;
            } return null;
        }

        public DataSet DS_SoGCS(int ma_bbg, string ma_tngan, string strMadviqly)
        {
            DataSet ds = new DataSet();
            Int64[] bb_giao;
            Int64[] id_hdon_CNOff;
            id_hdon_CNOff = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == strMadviqly && a.MA_TNGAN == ma_tngan
                             select a.ID_HDON).ToArray();
            if (id_hdon_CNOff.Count() == 0) return null;
            bb_giao = (from a in CMIS2.DB.CN_BB_GTHU
                       where a.MA_TNGAN == ma_tngan && a.SO_BBGIAO == ma_bbg
                       select a.SO_BBGIAO).ToArray();
            if (bb_giao.Count() == 0) return null;
            var so_gcs = (from b in CMIS2.DB.CN_HDON_GTHU
                          where bb_giao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == strMadviqly
                          select new { b.MA_SOGCS }).ToList();
            //var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO

            DataTable dt = Utility.LINQToDataTable(so_gcs);
            if (dt == null) return null;
            ds.Tables.Add(dt);
            return ds;
        }

        public DataSet select_ThuNganVien(string ma_dviqly)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                var result = from a in CMIS2.DB.D_TNGAN_VIEN
                             where a.MA_DVIQLY == ma_dviqly && a.TINH_TRANG == 1
                             select new
                             {
                                 a.MA_DVIQLY,
                                 a.MA_TNGAN,
                                 a.TEN_TNGAN,
                                 SEARCH = a.MA_TNGAN + " _*_ " + a.TEN_TNGAN
                             };

                dt = Utility.LINQToDataTable(result);
                if (dt == null) return null;
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                //maloi = ex.ToString();
                CMIS2.ResetDB(); return null;
            }
        }

        public string Ten_Tngan(string ma_tngan, string ma_dviqly)
        {
            string ten_tngan = "";
            var t_ngan = (from a in CMIS2.DB.D_TNGAN_VIEN
                          where a.MA_DVIQLY == ma_dviqly && a.MA_TNGAN == ma_tngan.ToUpper()
                          select new { a.TEN_TNGAN }).ToList();
            DataTable dt = Utility.LINQToDataTable(t_ngan);
            if (dt != null && dt.Rows.Count > 0)
            {
                ten_tngan = dt.Rows[0]["TEN_TNGAN"].ToString();
                return ten_tngan;
            }
            return null;
        }

        public DataSet LOAD_DSTon(string ma_tngan, string ma_dviqly)
        {
            Int64[] id_chamno_off;
            Int64[] id_hdon;
            Int64[] so_bbgiao;
            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dviqly && a.MA_TNGAN == ma_tngan
                             select a.ID_HDON).ToArray();
            if (id_chamno_off.Count() == 0) return null;

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dviqly && a.MA_TNGAN == ma_tngan
                         select a.SO_BBGIAO).ToArray();
            id_hdon = (from a in CMIS2.DB.CN_HDON_GTHU
                       where a.MA_DVIQLY == ma_dviqly && so_bbgiao.Contains(a.SO_BBGIAO)
                       select a.ID_HDON).ToArray();

            if (id_hdon == null) return null;

            var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                where !id_chamno_off.Contains(a.ID_HDON) && a.TIEN_NO > 0 && a.TTRANG_SSAI != 2 && a.MA_DVIQLY == ma_dviqly
                                select new
                                {
                                    TIEN_NOP = a.TIEN_NO,
                                    THUE_NOP = a.THUE_NO,
                                    TTIEN = a.TIEN_NO + a.THUE_NO,
                                    a.ID_HDON,
                                    a.MA_KHANG,
                                    a.TEN_KHANG,
                                    a.DCHI_KHANG,
                                    a.KY,
                                    a.THANG,
                                    a.NAM,
                                    a.LOAI_HDON
                                }).ToList();

            var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                where so_bbgiao.Contains(a.SO_BBGIAO) && a.MA_DVIQLY == ma_dviqly
                                select new { a.SO_BBGIAO, a.MA_SOGCS, a.ID_HDON }).ToList();

            var kq = (from a in cn_theodoino
                      join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                      select new
                      {
                          a.ID_HDON,
                          a.MA_KHANG,
                          a.TEN_KHANG,
                          a.DCHI_KHANG,
                          a.KY,
                          a.NAM,
                          a.THANG,
                          a.LOAI_HDON,
                          b.MA_SOGCS,
                          b.SO_BBGIAO,
                          TIEN_NO = a.TIEN_NOP,
                          THUE_NO = a.THUE_NOP,
                          TONG_TIEN = a.TTIEN
                      }).ToList();

            DataSet data = new DataSet();
            DataTable dt = Utility.LINQToDataTable(kq);
            if (dt == null) return null;
            data.Tables.Add(dt);
            return data;
        }

        public DataSet DSTon_ALL(string ma_dviqly, int thanght)
        {
            Int64[] id_chamno_off;
            Int64[] id_hdon;
            Int64[] so_bbgiao;
            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dviqly && a.THANG_HT == thanght
                             select a.ID_HDON).ToArray();
            if (id_chamno_off == null) return null;

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dviqly
                         select a.SO_BBGIAO).ToArray();
            id_hdon = (from a in CMIS2.DB.CN_HDON_GTHU
                       where a.MA_DVIQLY == ma_dviqly && so_bbgiao.Contains(a.SO_BBGIAO)
                       select a.ID_HDON).ToArray();

            if (id_hdon == null) return null;

            var cn_theodoino = from a in CMIS2.DB.CN_THEODOINO
                               where id_hdon.Contains(a.ID_HDON) && a.TTRANG_SSAI != 2 && a.TIEN_NO > 0 && a.MA_DVIQLY == ma_dviqly && !id_chamno_off.Contains(a.ID_HDON)
                               select new
                               {
                                   TIEN_NOP = a.TIEN_NO,
                                   THUE_NOP = a.THUE_NO,
                                   TTIEN = a.TIEN_NO + a.THUE_NO,
                                   a.ID_HDON,
                                   a.MA_KHANG,
                                   a.TEN_KHANG,
                                   a.DCHI_KHANG,
                                   a.KY,
                                   a.THANG,
                                   a.NAM,
                                   a.LOAI_HDON
                               };

            var cn_hdon_gthu = from a in CMIS2.DB.CN_HDON_GTHU
                               where so_bbgiao.Contains(a.SO_BBGIAO) && a.MA_DVIQLY == ma_dviqly
                               select new { a.SO_BBGIAO, a.MA_SOGCS, a.ID_HDON };

            var kq = from a in cn_theodoino
                     join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                     select new
                     {
                         a.ID_HDON,
                         a.MA_KHANG,
                         a.TEN_KHANG,
                         a.DCHI_KHANG,
                         a.KY,
                         a.NAM,
                         a.THANG,
                         a.LOAI_HDON,
                         b.MA_SOGCS,
                         b.SO_BBGIAO,
                         TIEN_NO = a.TIEN_NOP,
                         THUE_NO = a.THUE_NOP,
                         TONG_TIEN = a.TTIEN
                     };

            DataSet data = new DataSet();
            DataTable dt = Utility.LINQToDataTable(kq);
            if (dt == null) return null;
            data.Tables.Add(dt);
            return data;
        }

        public DataSet DS_TON_ALL(string ma_dvqly)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao == null) return null;



            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly
                                  select a.ID_HDON).ToArray();
            if (id_hdon_cn_offline.Count() > 0)
            {

                id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                             where so_bbgiao.Contains(b.SO_BBGIAO)
                             select b.ID_HDON).ToArray();
                if (id_hoadon.Count() > 0)
                {
                    var cn_hdon_gthu = (from b in CMIS2.DB.CN_HDON_GTHU
                                        where b.MA_DVIQLY == ma_dvqly
                                        select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();


                    var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                        where e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                        select e).ToList();
                    var kq = from a in cn_theodoino
                             join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                             select new
                             {
                                 a.ID_HDON,
                                 a.TEN_KHANG,
                                 a.DCHI_KHANG,
                                 a.MA_KHANG,
                                 a.KY,
                                 a.THANG,
                                 a.NAM,
                                 b.SO_BBGIAO,
                                 b.MA_SOGCS,
                                 a.TIEN_NO,
                                 a.THUE_NO,
                                 TONG_TIEN = a.TIEN_NO + a.THUE_NO
                             };
                    DataTable dt = Utility.LINQToDataTable(kq);
                    if (dt == null) return null;
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet DSTon_Nutcha(string ma_tngan, string ma_dvqly)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao == null) return null;



            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON).ToArray();
            if (id_hdon_cn_offline.Count() > 0)
            {

                id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                             where so_bbgiao.Contains(b.SO_BBGIAO)
                             select b.ID_HDON).ToArray();
                if (id_hoadon.Count() > 0)
                {
                    var cn_hdon_gthu = from b in CMIS2.DB.CN_HDON_GTHU
                                       where b.MA_DVIQLY == ma_dvqly
                                       select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS };


                    var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                        where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                        select new
                                        {

                                            e.MA_SOGCS,
                                            TONG_TIEN = e.TIEN_PSINH + e.THUE_PSINH,
                                            TIEN_NO = e.TIEN_PSINH,
                                            THUE_NO = e.THUE_PSINH,
                                            e.ID_HDON,
                                            e.KY,
                                            e.THANG,
                                            e.NAM,
                                            e.MA_KHANG,
                                            e.LOAI_HDON,
                                            e.TEN_KHANG,
                                            e.DCHI_KHANG,
                                        }).ToList();
                    var kq = (from a in cn_theodoino
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  a.MA_KHANG,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  b.SO_BBGIAO,
                                  b.MA_SOGCS,
                                  a.TIEN_NO,
                                  a.THUE_NO,
                                  TONG_TIEN = a.TONG_TIEN
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    if (dt == null) return null;
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;

        }

        public DataSet DSTon_Nutcha2(string ma_tngan, Int64 so_BBGIAO, string ma_dvqly)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();

            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where so_bbgiao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == ma_dvqly
                         select b.ID_HDON).ToArray();

            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON).ToArray();

            if (id_hdon_cn_offline.Count() > 0)
            {
                var cn_hdon_gthu = (from b in CMIS2.DB.CN_HDON_GTHU
                                    where so_bbgiao.Contains(b.SO_BBGIAO)
                                    select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                    where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                    select new
                                    {
                                        e.KY,
                                        e.MA_SOGCS,
                                        TONG_TIEN = e.TIEN_NO + e.THUE_NO,
                                        e.TIEN_NO,
                                        e.THUE_NO,
                                        e.ID_HDON,
                                        e.THANG,
                                        e.NAM,
                                        e.MA_KHANG,
                                        e.LOAI_HDON,
                                        e.TEN_KHANG,
                                        e.DCHI_KHANG
                                    }).ToList();
                var kq = (from c in cn_theodoino
                          join d in cn_hdon_gthu on c.ID_HDON equals d.ID_HDON
                          where d.SO_BBGIAO == so_BBGIAO
                          select new
                          {
                              d.SO_BBGIAO,
                              d.MA_SOGCS,
                              c.TONG_TIEN,
                              c.TIEN_NO,
                              c.THUE_NO,
                              c.ID_HDON,
                              c.THANG,
                              c.NAM,
                              c.MA_KHANG,
                              c.LOAI_HDON,
                              c.TEN_KHANG,
                              c.DCHI_KHANG,
                              c.KY
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                if (dt == null) return null;
                data.Tables.Add(dt);
                return data;
            }
            return null;
        }

        public DataSet DSTon_Nutcon(string ma_tngan, Int64 so_BBGIAO, string Ma_SoGCS, string ma_dvqly)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();

            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                         select b.ID_HDON).ToArray();

            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON).ToArray();

            if (id_hdon_cn_offline.Count() > 0)
            {
                var cn_hdon_gthu = (from b in CMIS2.DB.CN_HDON_GTHU
                                    where b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                                    select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                    where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                    select e).ToList();
                var kq = (from c in cn_theodoino
                          join d in cn_hdon_gthu on c.ID_HDON equals d.ID_HDON
                          select new
                          {
                              d.SO_BBGIAO,
                              d.MA_SOGCS,
                              c.TIEN_NO,
                              c.THUE_NO,
                              c.ID_HDON,
                              c.THANG,
                              c.NAM,
                              c.MA_KHANG,
                              c.LOAI_HDON,
                              c.TEN_KHANG,
                              c.DCHI_KHANG,
                              c.KY,
                              TONG_TIEN = c.TIEN_NO + c.THUE_NO
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                data.Tables.Add(dt);
                if (dt == null) return null;
                return data;
            }
            return null;
        }

        public DataSet LOAD_ATM(string ma_tngan, string ma_dvqly)
        {
            Int64[] id_hdon;
            Int64[] so_bb_gthu;
            string[] ma_gd;

            data = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            var gdich_atm = (from a in CMIS2.DB.NPC_GDICH_ATM
                             where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                             select new { a.NGAY_TTOAN }).ToList();

            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    so_bb_gthu = (from a in CMIS2.DB.CN_BB_GTHU
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.SO_BBGIAO).ToArray();
                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && so_bb_gthu.Contains(a.SO_BBGIAO)
                                        select a).ToList();

                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG,
                                      b.KY,
                                      b.THANG,
                                      b.NAM
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  a.NGAY_NOP,
                                  a.TIEN_GTGT,
                                  a.SO_TIEN,
                                  a.TONG_TIEN,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  b.MA_SOGCS,
                                  a.MA_KHANG,
                                  b.SO_BBGIAO,

                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet DS_TN_ATM(string ma_dvqly)
        {
            Int64[] id_hdon;
            Int64[] so_bb_gthu;
            string[] ma_gd;
            string[] tngan;
            data = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            var gdich_atm = (from a in CMIS2.DB.NPC_GDICH_ATM
                             where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                             select new { a.NGAY_TTOAN }).ToList();

            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    so_bb_gthu = (from a in CMIS2.DB.D_TNGAN_VIEN
                                  join b in CMIS2.DB.CN_BB_GTHU
                                  on a.MA_TNGAN equals b.MA_TNGAN
                                  where a.MA_DVIQLY == ma_dvqly
                                  select b.SO_BBGIAO).ToArray();

                    var tngan_bb = (from a in CMIS2.DB.D_TNGAN_VIEN
                                    join b in CMIS2.DB.CN_BB_GTHU
                                    on a.MA_TNGAN equals b.MA_TNGAN
                                    where a.MA_DVIQLY == ma_dvqly
                                    select new { a.TEN_TNGAN, a.MA_TNGAN, b.SO_BBGIAO }).ToList();

                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && so_bb_gthu.Contains(a.SO_BBGIAO)
                                        select new { a.ID_HDON, a.SO_BBGIAO }).ToList();

                    var cn_hdon_gthu2 = (from a in cn_hdon_gthu
                                         join b in tngan_bb on a.SO_BBGIAO equals b.SO_BBGIAO
                                         select new { b.TEN_TNGAN, b.MA_TNGAN, a.ID_HDON }).ToList();

                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG,
                                      b.KY,
                                      b.THANG,
                                      b.NAM
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu2 on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  b.MA_TNGAN,
                                  b.TEN_TNGAN
                              }).ToList();
                    tngan = kq.Select(c => c.MA_TNGAN).Distinct().ToArray();
                    var tngan2 = (from a in CMIS2.DB.D_TNGAN_VIEN
                                  where a.MA_DVIQLY == ma_dvqly && tngan.Contains(a.MA_TNGAN)
                                  select new { a.TEN_TNGAN, a.MA_TNGAN }).ToList();
                    DataTable dt = Utility.LINQToDataTable(tngan2);
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet DS_ATM_All(string ma_dvqly)
        {
            Int64[] id_hdon;
            Int64[] so_bb_gthu;
            string[] ma_gd;

            data = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            var gdich_atm = (from a in CMIS2.DB.NPC_GDICH_ATM
                             where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                             select new { a.NGAY_TTOAN }).ToList();

            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    so_bb_gthu = (from a in CMIS2.DB.CN_BB_GTHU
                                  where a.MA_DVIQLY == ma_dvqly
                                  select a.SO_BBGIAO).ToArray();
                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && so_bb_gthu.Contains(a.SO_BBGIAO)
                                        select a).ToList();

                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG,
                                      b.KY,
                                      b.THANG,
                                      b.NAM
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  NGAY_CHAMNO = a.NGAY_NOP,
                                  THUE_NOP = a.TIEN_GTGT,
                                  TIEN_NOP = a.SO_TIEN,
                                  TTIEN = a.TONG_TIEN,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  b.MA_SOGCS,
                                  a.MA_KHANG,
                                  b.SO_BBGIAO
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet DS_ATM_NUTCHA(string ma_tngan, string ma_dvqly)
        {
            Int64[] id_hdon;
            Int64[] so_bb_gthu;
            string[] ma_gd;

            data = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            var gdich_atm = (from a in CMIS2.DB.NPC_GDICH_ATM
                             where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                             select new { a.NGAY_TTOAN }).ToList();

            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    so_bb_gthu = (from a in CMIS2.DB.CN_BB_GTHU
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.SO_BBGIAO).ToArray();
                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && so_bb_gthu.Contains(a.SO_BBGIAO)
                                        select a).ToList();

                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG,
                                      b.KY,
                                      b.THANG,
                                      b.NAM
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  NGAY_CHAMNO = a.NGAY_NOP,
                                  THUE_NOP = a.TIEN_GTGT,
                                  TIEN_NOP = a.SO_TIEN,
                                  TTIEN = a.TONG_TIEN,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  b.MA_SOGCS,
                                  a.MA_KHANG,
                                  b.SO_BBGIAO
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet DS_ATM_NUTCHA2(int bb_giao, string ma_tngan, string ma_dvqly)
        {
            Int64[] id_hdon;
            string[] ma_gd;

            data = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            var gdich_atm = (from a in CMIS2.DB.NPC_GDICH_ATM
                             where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                             select new { a.NGAY_TTOAN }).ToList();

            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && a.SO_BBGIAO == bb_giao
                                        select a).ToList();

                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG,
                                      b.KY,
                                      b.THANG,
                                      b.NAM
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  NGAY_CHAMNO = a.NGAY_NOP,
                                  THUE_NOP = a.TIEN_GTGT,
                                  TIEN_NOP = a.SO_TIEN,
                                  TTIEN = a.TONG_TIEN,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  b.MA_SOGCS,
                                  a.MA_KHANG,
                                  b.SO_BBGIAO
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet DS_ATM_NUTCON(string so_gcs, int bb_giao, string ma_tngan, string ma_dvqly)
        {
            Int64[] id_hdon;
            string[] ma_gd;

            data = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            var gdich_atm = (from a in CMIS2.DB.NPC_GDICH_ATM
                             where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                             select new { a.NGAY_TTOAN }).ToList();

            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && a.SO_BBGIAO == bb_giao && a.MA_SOGCS == so_gcs
                                        select a).ToList();

                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG,
                                      b.KY,
                                      b.THANG,
                                      b.NAM
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  NGAY_CHAMNO = a.NGAY_NOP,
                                  THUE_NOP = a.TIEN_GTGT,
                                  TIEN_NOP = a.SO_TIEN,
                                  TTIEN = a.TONG_TIEN,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  b.MA_SOGCS,
                                  a.MA_KHANG,
                                  b.SO_BBGIAO
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet TNV_CHITIET_HDON(string ma_dviqly)
        {
            string[] TNV;
            Int64[] ID_HDON;
            Int64[] SO_BBGIAO;
            var CN_BB_GTHU = (from a in CMIS2.DB.CN_BB_GTHU
                              where a.MA_DVIQLY == ma_dviqly
                              select new { a.MA_TNGAN, a.SO_BBGIAO }).ToList();
            SO_BBGIAO = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dviqly
                         select a.SO_BBGIAO).ToArray();
            var CN_HDON_GTHU = (from a in CMIS2.DB.CN_HDON_GTHU
                                where a.MA_DVIQLY == ma_dviqly && SO_BBGIAO.Contains(a.SO_BBGIAO)
                                select new { a.ID_HDON, a.SO_BBGIAO }).ToList();

            var CN_THEODOINO = (from a in CMIS2.DB.CN_THEODOINO
                                where a.MA_DVIQLY == ma_dviqly
                                select new { a.ID_HDON }).ToList();
            var BB_HDON_GTHU = (from a in CN_BB_GTHU
                                join b in CN_HDON_GTHU on a.SO_BBGIAO equals b.SO_BBGIAO
                                select new { a.MA_TNGAN, b.ID_HDON }).ToList();
            var THEODOINO_BB_HDON = (from a in BB_HDON_GTHU
                                     join b in CN_THEODOINO on a.ID_HDON equals b.ID_HDON
                                     select new { a.MA_TNGAN }).ToList();
            TNV = THEODOINO_BB_HDON.Select(c => c.MA_TNGAN).Distinct().ToArray();
            var KQ = (from a in CMIS2.DB.D_TNGAN_VIEN
                      where a.MA_DVIQLY == ma_dviqly && TNV.Contains(a.MA_TNGAN)
                      select new { a.TEN_TNGAN, a.MA_TNGAN }).ToList();
            DataTable dt = Utility.LINQToDataTable(KQ);
            if (dt == null) return null;
            DataSet data = new DataSet();
            data.Tables.Add(dt);
            return data;
        }

        public DataSet HDON_CHAMNONHANH(string ma_dviqly, string ma_tngan, int id_hdon)
        {
            Int64[] SO_BBGIAO;
            Int64[] ID_HDON;
            var CN_BB_GTHU = (from a in CMIS2.DB.CN_BB_GTHU
                              where a.MA_DVIQLY == ma_dviqly && a.MA_TNGAN == ma_tngan
                              select new { a.MA_TNGAN, a.SO_BBGIAO }).ToList();
            SO_BBGIAO = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dviqly && a.MA_TNGAN == ma_tngan
                         select a.SO_BBGIAO).ToArray();
            if (SO_BBGIAO.Count() == 0) return null;
            var CN_HDON_GTHU = (from a in CMIS2.DB.CN_HDON_GTHU
                                where SO_BBGIAO.Contains(a.SO_BBGIAO) && a.MA_DVIQLY == ma_dviqly
                                select new { a.ID_HDON, a.MA_SOGCS, a.SO_BBGIAO }).ToList();

            var CN_THEODOINO = (from a in CMIS2.DB.CN_THEODOINO
                                where a.MA_DVIQLY == ma_dviqly && a.ID_HDON == id_hdon
                                select new
                                {
                                    a.ID_HDON,
                                    a.MA_KHANG,
                                    a.TEN_KHANG,
                                    a.DCHI_KHANG,
                                    KYTHANGNAM = a.KY + "-" + a.THANG + "/" + a.NAM,
                                    a.TIEN_NO,
                                    a.THUE_NO,
                                    TONGTIEN = a.TIEN_NO + a.THUE_NO,
                                    a.TTRANG_SSAI,
                                    a.LAN_GIAO,
                                    a.MA_SOGCS,
                                    a.STT
                                }).ToList();
            var KETQUA = (from a in CN_THEODOINO
                          join b in CN_HDON_GTHU on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              a.ID_HDON,
                              a.MA_KHANG,
                              a.TEN_KHANG,
                              a.DCHI_KHANG,
                              a.KYTHANGNAM,
                              a.TIEN_NO,
                              a.THUE_NO,
                              TONGTIEN = a.TIEN_NO + a.THUE_NO,
                              a.TTRANG_SSAI,
                              a.LAN_GIAO,
                              a.MA_SOGCS,
                              a.STT,
                              b.SO_BBGIAO
                          }
                          ).ToList();
            DataSet ds = new DataSet();
            DataTable dtb = new DataTable();
            dtb = Utility.LINQToDataTable(KETQUA);
            if (dtb == null) return null;
            ds.Tables.Add(dtb);
            return ds;
        }
    }
}
